/* 
 * File:   DAOBatchMerge.cpp
 * Author: Morpheus
 * 
 * Created on 29 juin 2013, 19:48
 */

#include "DAOBatchMerge.h"
#include <QtCore/qfile.h>
#include <QtCore/qfileinfo.h>
#include <QtCore/qdir.h>
#include <QtCore/qstringlist.h>
#include <QtCore/qdatastream.h>
#include <QtCore/qvariant.h>
#include <QtCore/qdebug.h>
#include <QtSql/qsqldatabase.h>
#include <QtSql/qsqlquery.h>
#include <QtSql/qsqlerror.h>
#include <QtCore/quuid.h>
#include <QtWidgets/qapplication.h>

const int BatchMergeCriteria::version = 1;
DAOBatchMerge *DAOBatchMerge::instance = 0;

BatchMergeCriteria::BatchMergeCriteria()
 : id(0), name(QString::fromUtf8("Personnalisée")), traited(Qt::PartiallyChecked), alwaysShowNotTraited(false),
   createdDateTagAdv(None), createdDateTag(Date), createdDate1(QDateTime::currentDateTime()), createdDate2(QDateTime::currentDateTime()),
   modifiedDateTagAdv(None), modifiedDateTag(Date), modifiedDate1(QDateTime::currentDateTime()), modifiedDate2(QDateTime::currentDateTime()) {
    qFill(relativeCreatedDate, relativeCreatedDate+4, 0);
    qFill(relativeModifiedDate, relativeModifiedDate+4, 0);
}
BatchMergeCriteria::BatchMergeCriteria(const BatchMergeCriteria& orig)
 : id(orig.id), name(orig.name), traited(orig.traited), alwaysShowNotTraited(orig.alwaysShowNotTraited),
   createdDateTagAdv(orig.createdDateTagAdv), createdDateTag(orig.createdDateTag),createdDate1(orig.createdDate1), createdDate2(orig.createdDate2),
   modifiedDateTagAdv(orig.modifiedDateTagAdv), modifiedDateTag(orig.modifiedDateTag), modifiedDate1(orig.modifiedDate1), modifiedDate2(orig.modifiedDate2) {
    qCopy(orig.relativeCreatedDate, orig.relativeCreatedDate+4, relativeCreatedDate);
    qCopy(orig.relativeModifiedDate, orig.relativeModifiedDate+4, relativeModifiedDate);
}
BatchMergeCriteria::~BatchMergeCriteria(){
}
void BatchMergeCriteria::updateDates(){
    if(createdDateTag >= RelativeDate){
        createdDate1 = QDateTime::currentDateTime();
        if(createdDateTag == RelativeDateTime){
            if(relativeCreatedDate[0] != 0)
                createdDate1 = createdDate1.addSecs(-relativeCreatedDate[0]*60);     // mins
            if(relativeCreatedDate[1] != 0)
                createdDate1 = createdDate1.addSecs(-relativeCreatedDate[1]*3600);   // hours
        }
        if(relativeCreatedDate[2] != 0)
            createdDate1 = createdDate1.addDays(-relativeCreatedDate[2]);        // days
        if(relativeCreatedDate[3] != 0)
            createdDate1 = createdDate1.addMonths(-relativeCreatedDate[3]);       // Mois
        createdDate2 = createdDate1;
        createdDateTag = TagDate(createdDateTag-2);
    }
    if(modifiedDateTag >= RelativeDate){
        modifiedDate1 = QDateTime::currentDateTime();
        if(modifiedDateTag >= RelativeDate){
            if(relativeModifiedDate[0] != 0)
                modifiedDate1 = createdDate1.addSecs(-relativeModifiedDate[0]*60);     // mins
            if(relativeModifiedDate[1] != 0)
                modifiedDate1 = createdDate1.addSecs(-relativeModifiedDate[1]*3600);   // hours
        }
        if(relativeModifiedDate[2] != 0)
            modifiedDate1 = createdDate1.addDays(-relativeModifiedDate[2]);        // days
        if(relativeModifiedDate[3] != 0)
            modifiedDate1 = createdDate1.addMonths(-relativeModifiedDate[3]);       // mois
        modifiedDate2 = modifiedDate1;
        modifiedDateTag = TagDate(modifiedDateTag-2);
    }
}

QDataStream& operator<<(QDataStream& s, const BatchMergeCriteria& obj){
    s << BatchMergeCriteria::version << QString("BatchMergeCriteria");
    s << int(obj.traited) << obj.alwaysShowNotTraited;
    s << int(obj.createdDateTagAdv) << int(obj.createdDateTag) << obj.createdDate1 << obj.createdDate2;
    s << int(obj.modifiedDateTagAdv) << int(obj.modifiedDateTag) << obj.modifiedDate1 << obj.modifiedDate2;
    for (int i = 0; i < 4; i++) {
        s << obj.relativeCreatedDate[i] << obj.relativeModifiedDate[i];
    }
    
    return s;
}
QDataStream& operator>>(QDataStream& s, BatchMergeCriteria& obj){
    QString tmp;        int version = 0;
    s >> version >> tmp;
    if(tmp != QString("BatchMergeCriteria")){
        s.setStatus(QDataStream::ReadCorruptData);
        return s;
    }
    int tagAdv, tag;
    s >> tag >> obj.alwaysShowNotTraited;
    obj.traited = Qt::CheckState(tag);
    s >> tagAdv >> tag >> obj.createdDate1 >> obj.createdDate2;
    obj.createdDateTagAdv = BatchMergeCriteria::TagDateAdv(tagAdv);     obj.createdDateTag = BatchMergeCriteria::TagDate(tag);
    s >> tagAdv >> tag >> obj.modifiedDate1 >> obj.modifiedDate2;
    obj.modifiedDateTagAdv = BatchMergeCriteria::TagDateAdv(tagAdv);     obj.modifiedDateTag = BatchMergeCriteria::TagDate(tag);
    for (int i = 0; i < 4; i++) {
        s >> obj.relativeCreatedDate[i] >> obj.relativeModifiedDate[i];
    }

    return s;
}

DAOBatchMerge::DAOBatchMerge() {
    QDir dir(qApp->applicationDirPath());
    QString path = dir.absoluteFilePath("./FileMergerData/FileMerger.sqlite");
    bool fileExist = QFile::exists(path);
    openDB();
    if(!fileExist){
        createDB();
    }
}

DAOBatchMerge::~DAOBatchMerge() {
    QSqlDatabase::removeDatabase("FileMerger");
}

DAOBatchMerge* DAOBatchMerge::self(){
    if(instance == NULL){
        instance = new DAOBatchMerge();
    }
    return instance;
}

QList<MyBatchMerge> DAOBatchMerge::getBatchMergeByCriteria(const BatchMergeCriteria& criteria, const QVariantList& moreBatchMerge){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    QList<MyBatchMerge> list;
    
    QString query = "SELECT * FROM t_BatchMergeV1";
    if(criteria.alwaysShowNotTraited && !moreBatchMerge.isEmpty()){
        query += " WHERE id IN (:ids) OR doWthSucces=:notDo OR (1=1";
    }else if(criteria.alwaysShowNotTraited){
        query += " WHERE doWthSucces=:notDo OR (1=1";
    }else if(!moreBatchMerge.isEmpty()){
        query += " WHERE id IN (:ids) OR (1=1";
    }else{
        query += " WHERE 1=1";
    }
    if(!moreBatchMerge.isEmpty()){
        query = query.replace(":ids", qVariantFromValue(moreBatchMerge).toStringList().join(','));
    }
    
    if(criteria.traited != Qt::PartiallyChecked){
        query += " AND doWthSucces=:traited";
    }
    if(criteria.createdDateTagAdv == BatchMergeCriteria::Since || criteria.createdDateTagAdv == BatchMergeCriteria::Beetween){
        query += " AND created >= :created1";
    }
    if(criteria.createdDateTagAdv == BatchMergeCriteria::Before || criteria.createdDateTagAdv == BatchMergeCriteria::Beetween){
        query += " AND created < :created2";
    }
    if(criteria.modifiedDateTagAdv == BatchMergeCriteria::Since || criteria.modifiedDateTagAdv == BatchMergeCriteria::Beetween){
        query += " AND modified >= :modified1";
    }
    if(criteria.modifiedDateTagAdv == BatchMergeCriteria::Before || criteria.modifiedDateTagAdv == BatchMergeCriteria::Beetween){
        query += " AND modified < :modified2";
    }
    if(criteria.alwaysShowNotTraited || !moreBatchMerge.isEmpty()){
        query += ")";
    }

    if(q.prepare(query)){
        if(criteria.alwaysShowNotTraited){
            q.bindValue(":notDo", false);
        }
//        if(!moreBatchMerge.isEmpty()){
//            q.bindValue(":ids", qVariantFromValue(moreBatchMerge).toStringList().join(','));
//        }
        if(criteria.traited != Qt::PartiallyChecked){
            q.bindValue(":traited", criteria.traited == Qt::Checked);
        }
        if(criteria.createdDateTagAdv == BatchMergeCriteria::Since || criteria.createdDateTagAdv == BatchMergeCriteria::Beetween){
            if(criteria.createdDateTag == BatchMergeCriteria::Date){
                q.bindValue(":created1", criteria.createdDate1.date().toString(Qt::ISODate));
            }else{
                q.bindValue(":created1", criteria.createdDate1.toString(Qt::ISODate));
            }
        }
        if(criteria.createdDateTagAdv == BatchMergeCriteria::Before || criteria.createdDateTagAdv == BatchMergeCriteria::Beetween){
            if(criteria.createdDateTag == BatchMergeCriteria::Date){
                q.bindValue(":created2", criteria.createdDate2.date().toString(Qt::ISODate));
            }else{
                q.bindValue(":created2", criteria.createdDate2.toString(Qt::ISODate));
            }
        }
        if(criteria.modifiedDateTagAdv == BatchMergeCriteria::Since || criteria.modifiedDateTagAdv == BatchMergeCriteria::Beetween){
            if(criteria.modifiedDateTag == BatchMergeCriteria::Date){
                q.bindValue(":modified1", criteria.modifiedDate1.date().toString(Qt::ISODate));
            }else{
                q.bindValue(":modified1", criteria.modifiedDate1.toString(Qt::ISODate));
            }
        }
        if(criteria.modifiedDateTagAdv == BatchMergeCriteria::Before || criteria.modifiedDateTagAdv == BatchMergeCriteria::Beetween){
            if(criteria.modifiedDateTag == BatchMergeCriteria::Date){
                q.bindValue(":modified2", criteria.modifiedDate2.date().toString(Qt::ISODate));
            }else{
                q.bindValue(":modified2", criteria.modifiedDate2.toString(Qt::ISODate));
            }
        }
        
        if(!q.exec()){
            qDebug() << db.lastError();
        }
//        qDebug() << q.executedQuery() << q.boundValues();
        while(q.next()){
            MyBatchMerge batch;
            batch.setBlockUpdate(true);
            batch.setUUID(q.value(0).toInt());
            batch.setName(q.value(1).toString());
            batch.setCreatedDate(QDateTime::fromString(q.value(2).toString(), Qt::ISODate));
            batch.setLastModifiedDate(QDateTime::fromString(q.value(3).toString(), Qt::ISODate));
            batch.setSaveFilePatern(q.value(4).toString());
            batch.setSaveFileFormat(q.value(5).toString());
            batch.setDoWithSuccess(q.value(6).toBool());
            batch.setBlockUpdate(false);
            
            list << batch;
        }
    }else{
        qDebug() << db.lastError();
    }
    
    return list;
}

MyBatchMerge DAOBatchMerge::getBatchMerge(const int& mergeId){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    MyBatchMerge batch;
            
    if(q.prepare("SELECT * FROM t_BatchMergeV1 WHERE id=:id")){
        q.bindValue(":id", mergeId);
        if(!q.exec()){
            qDebug() << db.lastError();
        }
        if(q.next()){
            batch.setBlockUpdate(true);
            batch.setUUID(mergeId);
            batch.setName(q.value(1).toString());
            batch.setCreatedDate(QDateTime::fromString(q.value(2).toString(), Qt::ISODate));
            batch.setLastModifiedDate(QDateTime::fromString(q.value(3).toString(), Qt::ISODate));
            batch.setSaveFilePatern(q.value(4).toString());
            batch.setSaveFileFormat(q.value(5).toString());
            batch.setDoWithSuccess(q.value(6).toBool());
            batch.setBatchList( getBatchUnit(mergeId) );
            batch.setBlockUpdate(false);
        }
    }else{
        qDebug() << db.lastError();
    }
    
    return batch;
}

void DAOBatchMerge::saveBatchMerge(MyBatchMerge& batch){
    if(batchMergeExist(batch.getUUID())){
        updateBatchMerge(batch);
    }else{
        insertBatchMerge(batch);
    }
    
    updateBatchUnit(batch.getBatchList(), batch.getUUID());
}
void DAOBatchMerge::insertBatchMerge(MyBatchMerge& batch){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    if(q.prepare("INSERT INTO t_BatchMergeV1 VALUES (null,:name,:created,:modified,:saveFilePattern,:saveFileFormat,:doWthSucces)")){
        q.bindValue(":name", batch.getName());
        q.bindValue(":created", batch.getCreatedDate().toString(Qt::ISODate));
        q.bindValue(":modified", batch.getLastModifiedDate().toString(Qt::ISODate));
        q.bindValue(":saveFilePattern", batch.getSaveFilePatern());
        q.bindValue(":saveFileFormat", batch.getSaveFileFormat());
        q.bindValue(":doWthSucces", batch.getDoWithSuccess());

        if(!q.exec()){
            qDebug() << db.lastError();
        }else{
            batch.setUUID(q.lastInsertId().toInt());
        }
    }else{
        qDebug() << db.lastError();
    }
}
void DAOBatchMerge::updateBatchMerge(const MyBatchMerge& batch){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    if(q.prepare("UPDATE t_BatchMergeV1 SET name=:name, modified=:modified, saveFilePattern=:saveFilePattern, saveFileFormat=:saveFileFormat, doWthSucces=:doWthSucces WHERE id=:id")){
        q.bindValue(":name", batch.getName());
        q.bindValue(":modified", batch.getLastModifiedDate().toString(Qt::ISODate));
        q.bindValue(":saveFilePattern", batch.getSaveFilePatern());
        q.bindValue(":saveFileFormat", batch.getSaveFileFormat());
        q.bindValue(":doWthSucces", batch.getDoWithSuccess());
        q.bindValue(":id", batch.getUUID());

        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }else{
        qDebug() << db.lastError();
    }
}

void DAOBatchMerge::updateBatchMergeDoWthSucces(const int& mergeId, const bool& succes) {
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    if(q.prepare("UPDATE t_BatchMergeV1 SET doWthSucces=:doWthSucces WHERE id=:id")){
        q.bindValue(":doWthSucces", succes);
        q.bindValue(":id", mergeId);

        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }else{
        qDebug() << db.lastError();
    }
}


void DAOBatchMerge::removeBatchMerge(const QList<int>& ids){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    QStringList list;
    foreach(int id, ids){
        list << QString::number(id);
    }
    QString query = QString("DELETE FROM t_BatchMergeV1 WHERE id IN (%1)").arg(list.join(','));
    if(q.prepare(query)){
//        q.bindValue(":id", list.join(','));
        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }else{
        qDebug() << db.lastError();
    }
    
    query = QString("DELETE FROM t_BatchMergeUnitV1 WHERE mergeId IN (%1)").arg(list.join(','));
    if(q.prepare(query)){
//        q.bindValue(":id", list.join(','));
        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }else{
        qDebug() << db.lastError();
    }
}

bool DAOBatchMerge::batchMergeExist(const int& id){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
            
    if(q.prepare("SELECT * FROM t_BatchMergeV1 WHERE id=:id")){
        q.bindValue(":id", id);
        if(!q.exec()){
            qDebug() << db.lastError();
        }
        return q.next();
    }else{
        qDebug() << db.lastError();
    }
    return false;
}

void DAOBatchMerge::openDB(){
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "FileMerger");
    QDir dir(qApp->applicationDirPath());
    QFileInfo infoFile(dir.absoluteFilePath("./FileMergerData/FileMerger.sqlite"));
    db.setDatabaseName(infoFile.absoluteFilePath());
//    db.setHostName("");
//    db.setPort(port);
    QString user, passwd;
    if (!db.open(user, passwd)) {
        qDebug() << db.lastError();
        db = QSqlDatabase();
        QSqlDatabase::removeDatabase("FileMerger");
    }
}
void DAOBatchMerge::createDB(){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    if(!q.exec("create TABLE t_BatchMergeV1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, created TEXT, modified TEXT, saveFilePattern TEXT, saveFileFormat TEXT, doWthSucces Boolean)")){
        qDebug() << db.lastError();
    }
    if(!q.exec("create TABLE t_BatchMergeCriteriaV1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, traited INTEGER, alwaysShowNotTraited Boolean,"
            "createdDateTagAdv INTEGER, createdDateTag INTEGER, createdDate1 TEXT, createdDate2 TEXT,"
            "modifiedDateTagAdv INTEGER, modifiedDateTag INTEGER, modifiedDate1 TEXT, modifiedDate2 TEXT,"
            "relativeCreatedDate0 INTEGER, relativeCreatedDate1 INTEGER, relativeCreatedDate2 INTEGER, relativeCreatedDate3 INTEGER,"
            "relativeModifiedDate0 INTEGER, relativeModifiedDate1 INTEGER, relativeModifiedDate2 INTEGER, relativeModifiedDate3 INTEGER,"
            "current Boolean)")){
        qDebug() << db.lastError();
    }
//    if(!q.exec("create TABLE t_BatchMergeAssociation (mergeId TEXT, unitId TEXT)")){
//        qDebug() << db.lastError();
//    }
    if(!q.exec("create TABLE t_BatchMergeUnitV1 (id TEXT PRIMARY KEY, content TEXT, isFile Boolean, mergeId INT)")){
        qDebug() << db.lastError();
    }
    if(!q.exec("create TABLE t_BddSetting (id TEXT PRIMARY KEY, value TEXT)")){
        qDebug() << db.lastError();
    }
    
    if(q.prepare("INSERT INTO t_BddSetting VALUES (:id,:value)")){
        q.bindValue(":id", "BddVersion");
        q.bindValue(":value", "1");
        if(!q.exec()){
            qDebug() << db.lastError();
        }
        
        q.bindValue(":id", "MergeEntityVersion");
        if(!q.exec()){
            qDebug() << db.lastError();
        }
        
        q.bindValue(":id", "UnitEntityVersion");
        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }else{
        qDebug() << db.lastError();
    }
    {
        BatchMergeCriteria criteria; criteria.name = QObject::tr("Tous");
        insertBatchCriteria(criteria);
        setCurrentBatchCriteria(criteria.id);
    }
    {
        BatchMergeCriteria criteria; criteria.name = QObject::tr("Aujourd'hui + Non Traitées");
        criteria.alwaysShowNotTraited = true;
        criteria.createdDateTagAdv = BatchMergeCriteria::Since;
        criteria.createdDateTag = BatchMergeCriteria::RelativeDate;
        insertBatchCriteria(criteria);
    }
    {
        BatchMergeCriteria criteria; criteria.name = QObject::tr("Depuis Hier + Non Traitées");
        criteria.alwaysShowNotTraited = true;
        criteria.createdDateTagAdv = BatchMergeCriteria::Since;
        criteria.createdDateTag = BatchMergeCriteria::RelativeDate;
        criteria.relativeCreatedDate[2] = 1;
        insertBatchCriteria(criteria);
    }
    {
        BatchMergeCriteria criteria; criteria.name = QObject::tr("Depuis 1h + Non Traitées");
        criteria.alwaysShowNotTraited = true;
        criteria.createdDateTagAdv = BatchMergeCriteria::Since;
        criteria.createdDateTag = BatchMergeCriteria::RelativeDateTime;
        criteria.relativeCreatedDate[1] = 1;
        insertBatchCriteria(criteria);
    }
    {
        BatchMergeCriteria criteria; criteria.name = QObject::tr("Depuis 2h + Non Traitées");
        criteria.alwaysShowNotTraited = true;
        criteria.createdDateTagAdv = BatchMergeCriteria::Since;
        criteria.createdDateTag = BatchMergeCriteria::RelativeDateTime;
        criteria.relativeCreatedDate[1] = 2;
        insertBatchCriteria(criteria);
    }
    {
        BatchMergeCriteria criteria; criteria.name = QObject::tr("Non Traitées uniquement");
        criteria.traited = Qt::Unchecked;
        insertBatchCriteria(criteria);
    }
}

QList<MyBatchUnit> DAOBatchMerge::getBatchUnit(const int& mergeId){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    QList<MyBatchUnit> list;
            
    if(q.prepare("SELECT * FROM t_BatchMergeUnitV1 WHERE mergeId=:mergeId")){
        q.bindValue(":mergeId", mergeId);
        if(!q.exec()){
            qDebug() << db.lastError();
        }
        while(q.next()){
            MyBatchUnit unit;
            unit.setUUID(q.value(0).toString());
            if(q.value(2).toBool()) { // if isFile
                unit.setFilePath(q.value(1).toString());
            }else{
                unit.setContentText(q.value(1).toString());
            }

            list << unit;
        }
    }else{
        qDebug() << db.lastError();
    }
    return list;
}

void DAOBatchMerge::updateBatchUnit(const QList<MyBatchUnit>& list, const int& mergeId){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    
    if(q.prepare("DELETE FROM t_BatchMergeUnitV1 WHERE mergeId=:mergeId")){
        q.bindValue(":mergeId", mergeId);
        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }
    if(list.isEmpty())
        return;
    
    if(q.prepare("INSERT INTO t_BatchMergeUnitV1 VALUES (:id,:content,:isFile,:mergeId)")){
        q.bindValue(":mergeId", mergeId);
        foreach(MyBatchUnit unit, list){
            q.bindValue(":id", unit.getUUID());
            bool isFile = unit.isFile();
            if(isFile){
                q.bindValue(":content", unit.getFilePath());
            }else{
                q.bindValue(":content", unit.getContentText());
            }
            q.bindValue(":isFile", isFile);

            if(!q.exec()){
                qDebug() << db.lastError();
            }
        }
    }else{
        qDebug() << db.lastError();
    }
}

void DAOBatchMerge::insertBatchCriteria(BatchMergeCriteria& criteria){    
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    /**
     * create TABLE t_BatchMergeCriteriaV1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, traited TINYINT, alwaysShowNotTraited Boolean,"
            "createdDateTagAdv TINYINT, createdDateTag TINYINT, createdDate1 TEXT, createdDate2 TEXT,"
            "modifiedDateTagAdv TINYINT, modifiedDateTag TINYINT, modifiedDate1 TEXT, modifiedDate2 TEXT,"
            "relativeCreatedDate0 INT, relativeCreatedDate1 INT, relativeCreatedDate2 INT, relativeCreatedDate3 INT,"
            "relativeModifiedDate0 INT, relativeModifiedDate1 INT, relativeModifiedDate2 INT, relativeModifiedDate3 INT,"
            "current Boolean)
     */
    
    if(q.prepare("INSERT INTO t_BatchMergeCriteriaV1 (name,traited,alwaysShowNotTraited,"
            "createdDateTagAdv,createdDateTag,createdDate1,createdDate2,"
            "modifiedDateTagAdv,modifiedDateTag,modifiedDate1,modifiedDate2,"
            "relativeCreatedDate0,relativeCreatedDate1,relativeCreatedDate2,relativeCreatedDate3,"
            "relativeModifiedDate0,relativeModifiedDate1,relativeModifiedDate2,relativeModifiedDate3,current)"
            " VALUES"
            " (:name,:traited,:alwaysShowNotTraited,"
            ":createdDateTagAdv,:createdDateTag,:createdDate1,:createdDate2,"
            ":modifiedDateTagAdv,:modifiedDateTag,:modifiedDate1,:modifiedDate2,"
            ":relativeCreatedDate0,:relativeCreatedDate1,:relativeCreatedDate2,:relativeCreatedDate3,"
            ":relativeModifiedDate0,:relativeModifiedDate1,:relativeModifiedDate2,:relativeModifiedDate3,"
            ":current)")){
        q.bindValue(":name", criteria.name);
        q.bindValue(":traited", int(criteria.traited));
        q.bindValue(":alwaysShowNotTraited", criteria.alwaysShowNotTraited);
        q.bindValue(":createdDateTagAdv", int(criteria.createdDateTagAdv));
        q.bindValue(":createdDateTag", int(criteria.createdDateTag));
        q.bindValue(":createdDate1", criteria.createdDate1.toString(Qt::ISODate));
        q.bindValue(":createdDate2", criteria.createdDate2.toString(Qt::ISODate));
        
        q.bindValue(":modifiedDateTagAdv", int(criteria.modifiedDateTagAdv));
        q.bindValue(":modifiedDateTag", int(criteria.modifiedDateTag));
        q.bindValue(":modifiedDate1", criteria.modifiedDate1.toString(Qt::ISODate));
        q.bindValue(":modifiedDate2", criteria.modifiedDate2.toString(Qt::ISODate));
        
        q.bindValue(":relativeCreatedDate0", criteria.relativeCreatedDate[0]);
        q.bindValue(":relativeCreatedDate1", criteria.relativeCreatedDate[1]);
        q.bindValue(":relativeCreatedDate2", criteria.relativeCreatedDate[2]);
        q.bindValue(":relativeCreatedDate3", criteria.relativeCreatedDate[3]);
        
        q.bindValue(":relativeModifiedDate0", criteria.relativeModifiedDate[0]);
        q.bindValue(":relativeModifiedDate1", criteria.relativeModifiedDate[1]);
        q.bindValue(":relativeModifiedDate2", criteria.relativeModifiedDate[2]);
        q.bindValue(":relativeModifiedDate3", criteria.relativeModifiedDate[3]);
        
        q.bindValue(":current", false);

        if(!q.exec()){
            qDebug() << db.lastError();
        }else{
            criteria.id = q.lastInsertId().toInt();
        }
    }else{
        qDebug() << db.lastError();
    }
}
void DAOBatchMerge::updateBatchCriteria(BatchMergeCriteria& criteria){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    if(q.prepare("UPDATE t_BatchMergeCriteriaV1 SET name=:name,traited=:traited,alwaysShowNotTraited=:alwaysShowNotTraited,"
            "createdDateTagAdv=:createdDateTagAdv,createdDateTag=:createdDateTag,createdDate1=:createdDate1,createdDate2=:createdDate2,"
            "modifiedDateTagAdv=:modifiedDateTagAdv,modifiedDateTag=:modifiedDateTag,modifiedDate1=:modifiedDate1,modifiedDate2=:modifiedDate2,"
            "relativeCreatedDate0=:relativeCreatedDate0,relativeCreatedDate1=:relativeCreatedDate1,relativeCreatedDate2=:relativeCreatedDate2,relativeCreatedDate3=:relativeCreatedDate3,"
            "relativeModifiedDate0=:relativeModifiedDate0,relativeModifiedDate1=:relativeModifiedDate1,relativeModifiedDate2=:relativeModifiedDate2,relativeModifiedDate3=:relativeModifiedDate3 "
            "WHERE id=:id")){
        q.bindValue(":name", criteria.name);
        q.bindValue(":traited", criteria.traited);
        q.bindValue(":alwaysShowNotTraited", criteria.alwaysShowNotTraited);
        q.bindValue(":createdDateTagAdv", int(criteria.createdDateTagAdv));
        q.bindValue(":createdDateTag", int(criteria.createdDateTag));
        q.bindValue(":createdDate1", criteria.createdDate1.toString(Qt::ISODate));
        q.bindValue(":createdDate2", criteria.createdDate2.toString(Qt::ISODate));
        
        q.bindValue(":modifiedDateTagAdv", int(criteria.modifiedDateTagAdv));
        q.bindValue(":modifiedDateTag", int(criteria.modifiedDateTag));
        q.bindValue(":modifiedDate1", criteria.modifiedDate1.toString(Qt::ISODate));
        q.bindValue(":modifiedDate2", criteria.modifiedDate2.toString(Qt::ISODate));
        
        q.bindValue(":relativeCreatedDate0", criteria.relativeCreatedDate[0]);
        q.bindValue(":relativeCreatedDate1", criteria.relativeCreatedDate[1]);
        q.bindValue(":relativeCreatedDate2", criteria.relativeCreatedDate[2]);
        q.bindValue(":relativeCreatedDate3", criteria.relativeCreatedDate[3]);
        
        q.bindValue(":relativeModifiedDate0", criteria.relativeModifiedDate[0]);
        q.bindValue(":relativeModifiedDate1", criteria.relativeModifiedDate[1]);
        q.bindValue(":relativeModifiedDate2", criteria.relativeModifiedDate[2]);
        q.bindValue(":relativeModifiedDate3", criteria.relativeModifiedDate[3]);
        
        q.bindValue(":id", criteria.id);

        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }else{
        qDebug() << db.lastError();
    }
}
void DAOBatchMerge::removeBatchCriteria(const int& id){
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
            
    if(q.prepare("DELETE FROM t_BatchMergeCriteriaV1 WHERE id=:id")){
        q.bindValue(":id", id);
        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }else{
        qDebug() << db.lastError();
    }
}
void DAOBatchMerge::setCurrentBatchCriteria(const int& id){
    //    create TABLE BatchMergeCriteria (id INT primary key, name TEXT, traited TINYINT, alwaysShowNotTraited Boolean,"
//            "createdDateTagAdv TINYINT, createdDateTag TINYINT, createdDate1 TEXT, createdDate2 TEXT,"
//            "modifiedDateTagAdv TINYINT, modifiedDateTag TINYINT, modifiedDate1 TEXT, modifiedDate2 TEXT,"
//            "relativeCreatedDate0 INT, relativeCreatedDate1 INT, relativeCreatedDate2 INT, relativeCreatedDate3 INT,"
//            "relativeModifiedDate0 INT, relativeModifiedDate1 INT, relativeModifiedDate2 INT, relativeModifiedDate3 INT,"
//            "current Boolean
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
    
    if(q.prepare("UPDATE t_BatchMergeCriteriaV1 SET current=:current WHERE current=:actualy")){
        q.bindValue(":current", false);
        q.bindValue(":actualy", true);
        if(!q.exec()){
            qDebug() << db.lastError();
            return;
        }
    }else{
        qDebug() << db.lastError();
        return;
    }
    
    if(q.prepare("UPDATE t_BatchMergeCriteriaV1 SET current=:current WHERE id=:id")){
        q.bindValue(":current", true);
        q.bindValue(":id", id);
        if(!q.exec()){
            qDebug() << db.lastError();
        }
    }else{
        qDebug() << db.lastError();
    }
}

BatchMergeCriteria DAOBatchMerge::getBatchCriteria(const int& id){
    BatchMergeCriteria criteria;
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
            
//    create TABLE t_BatchMergeCriteriaV1 (id INT primary key, name TEXT, traited TINYINT, alwaysShowNotTraited Boolean,"
//            "createdDateTagAdv TINYINT, createdDateTag TINYINT, createdDate1 TEXT, createdDate2 TEXT,"
//            "modifiedDateTagAdv TINYINT, modifiedDateTag TINYINT, modifiedDate1 TEXT, modifiedDate2 TEXT,"
//            "relativeCreatedDate0 INT, relativeCreatedDate1 INT, relativeCreatedDate2 INT, relativeCreatedDate3 INT,"
//            "relativeModifiedDate0 INT, relativeModifiedDate1 INT, relativeModifiedDate2 INT, relativeModifiedDate3 INT,"
//            "current Boolean
    if(q.prepare("SELECT * FROM t_BatchMergeCriteriaV1 WHERE id=:id")){
        q.bindValue(":id", id);
        if(!q.exec()){
            qDebug() << db.lastError();
        }else if(q.next()){
            criteria.id = q.value(0).toInt();
            criteria.name = q.value(1).toString();
            criteria.traited = Qt::CheckState(q.value(2).toInt());
            criteria.alwaysShowNotTraited = q.value(3).toBool();
            criteria.createdDateTagAdv = BatchMergeCriteria::TagDateAdv(q.value(4).toInt());
            criteria.createdDateTag = BatchMergeCriteria::TagDate(q.value(5).toInt());
            criteria.createdDate1 = q.value(6).toDateTime();
            criteria.createdDate2 = q.value(7).toDateTime();
            criteria.modifiedDateTagAdv = BatchMergeCriteria::TagDateAdv(q.value(8).toInt());
            criteria.modifiedDateTag = BatchMergeCriteria::TagDate(q.value(9).toInt());
            criteria.modifiedDate1 = q.value(10).toDateTime();
            criteria.modifiedDate2 = q.value(11).toDateTime();
            criteria.relativeCreatedDate[0] = q.value(12).toInt();
            criteria.relativeCreatedDate[1] = q.value(13).toInt();
            criteria.relativeCreatedDate[2] = q.value(14).toInt();
            criteria.relativeCreatedDate[3] = q.value(15).toInt();
            criteria.relativeModifiedDate[0] = q.value(16).toInt();
            criteria.relativeModifiedDate[1] = q.value(17).toInt();
            criteria.relativeModifiedDate[2] = q.value(18).toInt();
            criteria.relativeModifiedDate[3] = q.value(19).toInt();
        }
    }else{
        qDebug() << db.lastError();
    }
    return criteria;
}
BatchMergeCriteria DAOBatchMerge::getCurrentBatchCriteria(){
    BatchMergeCriteria criteria;
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
            
//    create TABLE t_BatchMergeCriteriaV1 (id INT primary key, name TEXT, traited TINYINT, alwaysShowNotTraited Boolean,"
//            "createdDateTagAdv TINYINT, createdDateTag TINYINT, createdDate1 TEXT, createdDate2 TEXT,"
//            "modifiedDateTagAdv TINYINT, modifiedDateTag TINYINT, modifiedDate1 TEXT, modifiedDate2 TEXT,"
//            "relativeCreatedDate0 INT, relativeCreatedDate1 INT, relativeCreatedDate2 INT, relativeCreatedDate3 INT,"
//            "relativeModifiedDate0 INT, relativeModifiedDate1 INT, relativeModifiedDate2 INT, relativeModifiedDate3 INT,"
//            "current Boolean
    if(q.prepare("SELECT * FROM t_BatchMergeCriteriaV1 WHERE current=:current")){
        q.bindValue(":current", true);
        if(!q.exec()){
            qDebug() << db.lastError();
        }else if(q.next()){
            criteria.id = q.value(0).toInt();
            criteria.name = q.value(1).toString();
            criteria.traited = Qt::CheckState(q.value(2).toInt());
            criteria.alwaysShowNotTraited = q.value(3).toBool();
            criteria.createdDateTagAdv = BatchMergeCriteria::TagDateAdv(q.value(4).toInt());
            criteria.createdDateTag = BatchMergeCriteria::TagDate(q.value(5).toInt());
            criteria.createdDate1 = q.value(6).toDateTime();
            criteria.createdDate2 = q.value(7).toDateTime();
            criteria.modifiedDateTagAdv = BatchMergeCriteria::TagDateAdv(q.value(8).toInt());
            criteria.modifiedDateTag = BatchMergeCriteria::TagDate(q.value(9).toInt());
            criteria.modifiedDate1 = q.value(10).toDateTime();
            criteria.modifiedDate2 = q.value(11).toDateTime();
            criteria.relativeCreatedDate[0] = q.value(12).toInt();
            criteria.relativeCreatedDate[1] = q.value(13).toInt();
            criteria.relativeCreatedDate[2] = q.value(14).toInt();
            criteria.relativeCreatedDate[3] = q.value(15).toInt();
            criteria.relativeModifiedDate[0] = q.value(16).toInt();
            criteria.relativeModifiedDate[1] = q.value(17).toInt();
            criteria.relativeModifiedDate[2] = q.value(18).toInt();
            criteria.relativeModifiedDate[3] = q.value(19).toInt();
        }
    }else{
        qDebug() << db.lastError();
    }
    return criteria;
}
QList<BatchMergeCriteria> DAOBatchMerge::getAllBatchCriteria(){
    QList<BatchMergeCriteria> list;
    QSqlDatabase db = QSqlDatabase::database("FileMerger");
    QSqlQuery q(db);
            
    if(q.prepare("SELECT * FROM t_BatchMergeCriteriaV1")){
        if(!q.exec()){
            qDebug() << db.lastError();
        }else{
            while(q.next()){
                BatchMergeCriteria criteria;
                criteria.id = q.value(0).toInt();
                criteria.name = q.value(1).toString();
                criteria.traited = Qt::CheckState(q.value(2).toInt());
                criteria.alwaysShowNotTraited = q.value(3).toBool();
                criteria.createdDateTagAdv = BatchMergeCriteria::TagDateAdv(q.value(4).toInt());
                criteria.createdDateTag = BatchMergeCriteria::TagDate(q.value(5).toInt());
                criteria.createdDate1 = q.value(6).toDateTime();
                criteria.createdDate2 = q.value(7).toDateTime();
                criteria.modifiedDateTagAdv = BatchMergeCriteria::TagDateAdv(q.value(8).toInt());
                criteria.modifiedDateTag = BatchMergeCriteria::TagDate(q.value(9).toInt());
                criteria.modifiedDate1 = q.value(10).toDateTime();
                criteria.modifiedDate2 = q.value(11).toDateTime();
                criteria.relativeCreatedDate[0] = q.value(12).toInt();
                criteria.relativeCreatedDate[1] = q.value(13).toInt();
                criteria.relativeCreatedDate[2] = q.value(14).toInt();
                criteria.relativeCreatedDate[3] = q.value(15).toInt();
                criteria.relativeModifiedDate[0] = q.value(16).toInt();
                criteria.relativeModifiedDate[1] = q.value(17).toInt();
                criteria.relativeModifiedDate[2] = q.value(18).toInt();
                criteria.relativeModifiedDate[3] = q.value(19).toInt();

                list << criteria;
            }
        }
    }else{
        qDebug() << db.lastError();
    }
    return list;
}
